import numpy as np

if __name__ == '__main__':
    import pandas as pd

    max_meter = 20000
    min_meter = 200
    df = pd.read_csv("D:\\网管\\0101_0131最新sn数据.csv", usecols=['小区', 'ONU测距(m)'])
    grouped = df.groupby('小区')['ONU测距(m)']
    df['std'] = grouped.transform("std")
    df['mean'] = grouped.transform("mean")
    df.to_csv("/temp/t.csv")


    # 定义筛选函数
    def filter_within_std(group):
        mean_distance = group.mean()
        std_distance = group.std()
        return df.loc[group.index, :][np.abs(group - mean_distance) < std_distance]


    # 对每个分组应用筛选函数
    df_filter = grouped.apply(filter_within_std).reset_index(drop=True)

    print("筛选后的数据：")

    # df_filter = df[(df['ONU测距(m)'] > min_meter) & (df['ONU测距(m)'] < max_meter)]
    # print(len(df_filter))
    avg = df_filter['ONU测距(m)'].mean()
    median = df_filter['ONU测距(m)'].median()
    print('平均值：', avg)
    print('中位数：', median)
    # print(df['ONU测距(m)'].max())
    df = df_filter.groupby('小区')['ONU测距(m)'].mean()
    cell = len(df)

    print("小区数", cell)
    a = len(df[df < 2000])
    b = len(df[(df <= 6000) & (df >= 2000)])
    c = len(df[df > 6000])
    # df[df > 20000].reset_index().to_excel("/temp/t.xlsx")
    print("0-2km小区数", a)
    print("2-6km小区数", b)
    print(">6km小区数", c)
    print(a + b + c)
    # print(
    #     f"当前全市共{cell}个家宽小区，剔除异常数据后（接入距离大于{max_meter}和小于{min_meter}）后，ONU到OLT平均接入距离为{avg}，中位数为{median}，"
    #     f"其中接入距离0-2km{a}个小区，2-6km{b}个小区，6km以上{c}个小区。")
